package com.pennphoto.server;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

import com.google.gwt.event.dom.client.ClickEvent;
import com.google.gwt.event.dom.client.ClickHandler;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.pennphoto.client.PennPhotoCRUDService;
import com.pennphoto.client.ViewedPhotos;
import com.pennphoto.client.ViewedType;
import com.pennphoto.shared.PennPhotoConstants;

public class PennPhotoCRUDServiceImpl extends RemoteServiceServlet implements PennPhotoCRUDService{

	public static QueryServiceImpl engine;
	public Connection conn;
 
	
	public PennPhotoCRUDServiceImpl() throws Exception
	{
		 Class.forName ("oracle.jdbc.OracleDriver");

	     conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
	     
	}
	@Override
	public ArrayList<ViewedPhotos> getPhotoStream(int userId) throws Exception {
		// TODO Auto-generated method stub
		CallableStatement proc = conn.prepareCall("BEGIN "+PennPhotoConstants.schemaName+"updateScore(?); END;");
		proc.setInt(1, userId);
		proc.executeUpdate();
		com.pennphoto.client.ObjectFactory factory = new com.pennphoto.client.ObjectFactory();
		 ArrayList<ViewedType> views =  new ArrayList<ViewedType>();
		 ArrayList<Integer> pids =new ArrayList<Integer>();
		 
		 ArrayList<ViewedPhotos> vieweds = new ArrayList<ViewedPhotos>();
		 ArrayList<Integer> vids=new ArrayList<Integer>();
		HashMap<Integer,String> desc = new HashMap<Integer,String>();
		HashMap<Integer,String> urls = new HashMap<Integer,String>();
		HashMap<Integer,String> tags = new HashMap<Integer,String>();
		 PreparedStatement pstm = conn.prepareStatement("SELECT photoid,url,description,tag FROM "+PennPhotoConstants.schemaName+"photos WHERE uploadedBy=?", ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		 pstm.setInt(1, userId);
		 
		 ResultSet rset0 = pstm.executeQuery();
		 int rowCount=0;
		 if (rset0.last()) {
				rowCount = rset0.getRow();
				  rset0.beforeFirst(); 
			}
			//does the user have photos?
		if(rowCount>0)
		{
			//get basic details
			while(rset0.next())
			{
				Integer pi =rset0.getInt(1);
				pids.add(pi);
				urls.put(pi,rset0.getString(2));
				desc.put(pi,rset0.getString(3));
				tags.put(pi, rset0.getString(4));
			}
			
			
			//get viewed records for these photos if they exist
			String st1 = "SELECT viewerid, photoid,rate,comments FROM "+PennPhotoConstants.schemaName+"viewed  WHERE photoid in (%s)";
			st1=String.format(st1,preparePlaceHolders(pids.size()));
		 
			PreparedStatement pstmt = conn.prepareStatement(st1,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			for (int i = 0; i < pids.size(); i++) 
				 pstmt.setObject(i + 1, pids.get(i));
			 //pstmt.setInt(1,userId);
			 ResultSet rset = pstmt.executeQuery();
			
			rowCount=0;
			if (rset.last()) {
				rowCount = rset.getRow();
				  rset.beforeFirst(); 
			}
			//pids.clear();
			//are there viewers for these photos?
			ArrayList<Integer> donePhotos = new ArrayList<Integer>();
			if(rowCount!=0)
			{
				 while(rset.next())
				 {
					 ViewedType view = factory.createViewedType();
					 int vid = rset.getInt(1);
					 int pid = rset.getInt(2);
					 int rate = rset.getInt(3);
					 //pids.add(pid);
					 String comments=rset.getString(4);
					
					 vids.add(vid);
					 view.setViewerId(vid);
					 view.setRate(rate);
					 view.setComment(comments);
					 view.setPhotoId(pid);
					 views.add(view);
					 
				 }
				 //get user names of viewers					
				 String st3="SELECT userid,firstname,lastname FROM "+PennPhotoConstants.schemaName+"users WHERE userid in (%s)";
				 st3=String.format(st3,preparePlaceHolders(vids.size()));
				 PreparedStatement pstmt3 = conn.prepareStatement(st3);
				 for (int i = 0; i < vids.size(); i++) 
					 pstmt3.setObject(i + 1, vids.get(i));
				 HashMap<Integer,String> viewers = new HashMap<Integer,String>();
				 
				 ResultSet rset3 = pstmt3.executeQuery();
				 while (rset3.next())
				 {
					 int uid=rset3.getInt(1);
					 String name=rset3.getString(2)+" "+rset3.getString(3);
					 viewers.put(uid,name);
				 }
					//populate data object		
					for(int p:urls.keySet())
					{
						ViewedPhotos vp = new ViewedPhotos();
						vp.setPhotoid(p);
						vp.setUrl(urls.get(p));
						ArrayList<Integer> viewids = new ArrayList<Integer>();
						ArrayList<String> pviewers = new ArrayList<String>();
						ArrayList<String> comments = new ArrayList<String>();
						
						for(ViewedType v:views)
						{
							if(v.getPhotoId()==p)
							{
								if(viewers.containsKey(v.getViewerId()))
								{
									viewids.add(v.getViewerId());
									pviewers.add(viewers.get(v.getViewerId()));
									comments.add(v.getComment());
									vp.setDesc(desc.get(v.getPhotoId()));
									vp.setTag(tags.get(p));
								}
								
							}
						}
						vp.setViewerids(viewids);
						vp.setViewers(pviewers);
						vp.setComments(comments);
						vieweds.add(vp);
						//remove key to retain only those photos not put into objects yet
						donePhotos.add(p);
					}
					
					 rset.close();
					 
					 rset3.close();
						pstmt.close();
						
						pstmt3.close();
						//conn.close();
			}
			//for photos with no viewers:
			for(Integer g : donePhotos)
				urls.remove(g);
					 			
			 /*PreparedStatement pstmt2 = conn.prepareStatement("SELECT photoid,url,description,score FROM "+PennPhotoConstants.schemaName+"photos WHERE uploadedby = ? AND photoid NOT IN (SELECT photoid FROM "+PennPhotoConstants.schemaName+"viewed)",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			 pstmt2.setInt(1,userId);
			 ResultSet rset2 = pstmt2.executeQuery();
			 
			 HashMap<Integer,Float> rating = new HashMap<Integer,Float>();
			 if (rset2.last()) {
					rowCount = rset2.getRow();
					  rset2.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
			 }
			 urls.clear();
			 desc.clear();
			if(rowCount!=0)
			{
			 while (rset2.next())
			 {
				 int pid =rset2.getInt(1);
				 urls.put(pid,rset2.getString(2));
				 desc.put(pid,rset2.getString(3));
				 rating.put(pid, rset2.getFloat(4));
			 }*/
			 for(int p:urls.keySet())
				{
					ViewedPhotos vp = new ViewedPhotos();
					vp.setPhotoid(p);
					vp.setUrl(urls.get(p));					
					vp.setDesc(desc.get(p));
					//vp.setScore(rating.get(p));
					vp.setTag(tags.get(p));
					vieweds.add(vp);
				}
			//}
			//rset2.close();
		}
		return vieweds;
	}
		private String preparePlaceHolders(int length) {
		    StringBuilder builder = new StringBuilder();
		    for (int i = 0; i < length;) {
		        builder.append("?");
		        if (++i < length) {
		            builder.append(",");
		        }
		    }
		    return builder.toString();
		}
		
		public int insertPhoto(int userId, String description, String tag, String url, ArrayList<String> cnames,ArrayList<String> fnames, boolean isPublic) throws Exception
		{
			int ret = 0,maxphotoId=0;
			Statement stmt1=conn.createStatement();
			 ResultSet rset1 = stmt1.executeQuery("select max(photoid) from "+PennPhotoConstants.schemaName+"photos");
			 while (rset1.next()){
		           
				 maxphotoId=rset1.getInt(1)+1;
		            // Print col 1
		         }
			
			int i=(isPublic?0:1);
			String sql="INSERT INTO "+PennPhotoConstants.schemaName+"photos(photoid,tag, description, url,score,uploadedby,ispublic,uploadeddate,modifieddate) VALUES("+String.valueOf(maxphotoId)+",'"+tag+"','"+description+"','"+url+"',"+String.valueOf(0)+","+String.valueOf(userId)+","+String.valueOf(i)+",SYSDATE,SYSDATE)";
			//String sql="INSERT INTO "+PennPhotoConstants.schemaName+"photos(photoid,tag, description, url,score,uploadedby,uploadeddate,modifieddate) VALUES(?,?,?,?,?,?,SYSDATE,SYSDATE)";
			System.out.println(sql);
			Statement pstmt = conn.createStatement();
			ret = pstmt.executeUpdate(sql);
			
			String sql2 = "INSERT INTO "+PennPhotoConstants.schemaName+"visiblity(photoid,viewerid,reason) VALUES("+String.valueOf(maxphotoId)+","+String.valueOf(userId)+",'owner')";
			Statement stmt2 = conn.createStatement();
			ret=stmt2.executeUpdate(sql2);		
			
			if(cnames!=null && cnames.size()>0)
			{
				// get friends belonging to circles
				ArrayList<Integer> friendids = new ArrayList<Integer>();
				String sql3 = "SELECT friendid FROM "+PennPhotoConstants.schemaName+"circle WHERE circlename IN (%s) AND ownerid =?";
				sql3=String.format(sql3,preparePlaceHolders(cnames.size()));
				PreparedStatement pstmt1 = conn.prepareStatement(sql3,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
				for (int j = 0; j < cnames.size(); j++) 
					 pstmt1.setObject(j + 1, cnames.get(j));
				pstmt1.setInt(cnames.size()+1, userId);
				ResultSet rset = pstmt1.executeQuery();
				while(rset.next())
				{
					friendids.add(rset.getInt(1));
				}
				String sql4 ="";
				Statement stmt3=conn.createStatement();
				for(int fid:friendids){
					sql4 = "INSERT INTO "+PennPhotoConstants.schemaName+"visiblity(photoid,viewerid,reason) VALUES("+String.valueOf(maxphotoId)+","+String.valueOf(fid)+",'part of circle')";
					ret=stmt3.executeUpdate(sql4);
				}
			}
			
			if(fnames!=null && fnames.size()>0)
			{
				//get friend ids for names
				ArrayList<Integer> friendids = new ArrayList<Integer>();
				HashMap<String,String> names = new HashMap<String,String>();
				for(String s:fnames)
				{
					String[] nms = s.split(" ");
					names.put(nms[1], nms[0]);
				}
				String sql31 = "";
				Statement stmt3 = conn.createStatement();
				for(String n:names.keySet())
				{
					sql31="select userid from "+PennPhotoConstants.schemaName+"users where lastname='"+n+"' AND firstname ='"+names.get(n)+"'";
					ResultSet r0 = stmt3.executeQuery(sql31);
					while(r0.next())
						friendids.add(r0.getInt(1));
				}
				
				/*//String sql3 = "SELECT friendid FROM "+PennPhotoConstants.schemaName+"friends WHERE friendid IN (%s) AND ownerid =?";
				//sql3=String.format(sql3,preparePlaceHolders(cnames.size()));
				PreparedStatement pstmt1 = conn.prepareStatement(sql3,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
				for (int j = 0; j < cnames.size(); j++) 
					 pstmt1.setObject(j + 1, cnames.get(j));
				pstmt1.setInt(cnames.size()+1, userId);
				ResultSet rset = pstmt1.executeQuery();
				while(rset.next())
				{
					friendids.add(rset.getInt(1));
				}*/
				String sql4 ="";
				Statement stmt4=conn.createStatement();
				for(int fid:friendids){
					sql4 = "INSERT INTO "+PennPhotoConstants.schemaName+"visiblity(photoid,viewerid,reason) VALUES("+String.valueOf(maxphotoId)+","+String.valueOf(fid)+",'friend of owner')";
					ret=stmt4.executeUpdate(sql4);
				}
			}
			
			return ret;
		}
		
		
		public int updateViewedPhoto(int userId, int photoId, int rating, String comment) throws Exception
		{
			int ret = 0;
			Statement pstmt= conn.createStatement();
			Statement stmt1 = conn.createStatement();
			String sql1 = "SELECT viewerid,photoid FROM "+PennPhotoConstants.schemaName+"viewed WHERE viewerid="+String.valueOf(userId)+" AND photoid="+String.valueOf(photoId);
			ResultSet rset = stmt1.executeQuery(sql1);
			if(rset.next())
			{
				String sql2 = "UPDATE "+PennPhotoConstants.schemaName+"viewed SET comments="+comment+", rate="+String.valueOf(rating)+" WHERE  viewerid="+String.valueOf(userId)+" AND photoid="+String.valueOf(photoId);
				Statement stmt2 = conn.createStatement();
				ret=stmt2.executeUpdate(sql2);
			}
			else{
			String sql="INSERT into "+PennPhotoConstants.schemaName+"viewers VALUES("+String.valueOf(photoId)+","+String.valueOf(userId)+","+comment+","+String.valueOf(rating)+", TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS'), TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') ";
			System.out.println(sql);
			ret=pstmt.executeUpdate("INSERT into "+PennPhotoConstants.schemaName+"viewed(photoid,viewerid,comments,rate,modifieddate) VALUES("+String.valueOf(photoId)+","+String.valueOf(userId)+",'"+comment+"',"+String.valueOf(rating)+", SYSDATE) ");
			
			CallableStatement proc = conn.prepareCall("BEGIN "+PennPhotoConstants.schemaName+"updateScore(?); END;");
			proc.setInt(1, userId);
			proc.executeUpdate();
			}
			return ret;
		}
		
		public int deletePhoto(int userId, int photoId) throws Exception
		{
			int ret = 0;
			PreparedStatement pstmt= conn.prepareStatement("DELETE FROM "+PennPhotoConstants.schemaName+"photos WHERE photoId = ?");
			pstmt.setInt(1,photoId);
			ret=pstmt.executeUpdate();
			
			
			CallableStatement proc = conn.prepareCall("BEGIN "+PennPhotoConstants.schemaName+"updateScore(?); END;");
			proc.setInt(1, userId);
			proc.executeUpdate();
			return ret;
		}
		
		public ArrayList<ViewedPhotos> searchForPhotos(String query, int userId) throws Exception {
			// TODO Auto-generated method stub
			CallableStatement proc = conn.prepareCall("BEGIN "+PennPhotoConstants.schemaName+"updateScore(?); END;");
			proc.setInt(1, userId);
			proc.executeUpdate();
			com.pennphoto.client.ObjectFactory factory = new com.pennphoto.client.ObjectFactory();
			 ArrayList<ViewedType> views =  new ArrayList<ViewedType>();
			 ArrayList<Integer> pids =new ArrayList<Integer>();
			 
			 ArrayList<ViewedPhotos> vieweds = new ArrayList<ViewedPhotos>();
			 ArrayList<Integer> vids=new ArrayList<Integer>();
			HashMap<Integer,String> desc = new HashMap<Integer,String>();
			HashMap<Integer,String> urls = new HashMap<Integer,String>();
			
			
			 //PreparedStatement pstm = conn.prepareStatement("SELECT photoid,url,description,tag FROM "+PennPhotoConstants.schemaName+"photos WHERE tag like ? AND photoid IN (SELECT photoid FROM "+PennPhotoConstants.schemaName+"visiblity WHERE viewerid= ?)", ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			 PreparedStatement pstm = conn.prepareStatement("SELECT photoid,url,description,tag FROM "+PennPhotoConstants.schemaName+"photos where contains(tag,?,1) > 0 AND photoid IN (SELECT photoid from "+PennPhotoConstants.schemaName+"visiblity WHERE viewerid =?)  order by score desc", ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE );
			pstm.setString(1,"'"+query+"'");
			 pstm.setInt(2, userId);
			 
			 ResultSet rset0 = pstm.executeQuery();
			 int rowCount=0;
			 if (rset0.last()) {
					rowCount = rset0.getRow();
					  rset0.beforeFirst(); 
				}
				//does the user have photos?
			if(rowCount>0)
			{
				//get basic details
				while(rset0.next())
				{
					Integer pi =rset0.getInt(1);
					pids.add(pi);
					urls.put(pi,rset0.getString(2));
					desc.put(pi,rset0.getString(3));
				}
				
				
				//get viewed records for these photos if they exist
				String st1 = "SELECT viewerid, photoid,rate,comments FROM "+PennPhotoConstants.schemaName+"viewed  WHERE photoid in (%s)";
				st1=String.format(st1,preparePlaceHolders(pids.size()));
			 
				PreparedStatement pstmt = conn.prepareStatement(st1,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
				for (int i = 0; i < pids.size(); i++) 
					 pstmt.setObject(i + 1, pids.get(i));
				 //pstmt.setInt(1,userId);
				 ResultSet rset = pstmt.executeQuery();
				
				rowCount=0;
				if (rset.last()) {
					rowCount = rset.getRow();
					  rset.beforeFirst(); 
				}
				//pids.clear();
				//are there viewers for these photos?
				if(rowCount!=0)
				{
					 while(rset.next())
					 {
						 ViewedType view = factory.createViewedType();
						 int vid = rset.getInt(1);
						 int pid = rset.getInt(2);
						 int rate = rset.getInt(3);
						 //pids.add(pid);
						 String comments=rset.getString(4);
						
						 vids.add(vid);
						 view.setViewerId(vid);
						 view.setRate(rate);
						 view.setComment(comments);
						 view.setPhotoId(pid);
						 views.add(view);
						 
					 }
					 //get user names of viewers					
					 String st3="SELECT userid,firstname,lastname FROM "+PennPhotoConstants.schemaName+"users WHERE userid in (%s)";
					 st3=String.format(st3,preparePlaceHolders(vids.size()));
					 PreparedStatement pstmt3 = conn.prepareStatement(st3);
					 for (int i = 0; i < vids.size(); i++) 
						 pstmt3.setObject(i + 1, vids.get(i));
					 HashMap<Integer,String> viewers = new HashMap<Integer,String>();
					 ResultSet rset3 = pstmt3.executeQuery();
					 while (rset3.next())
					 {
						 int uid=rset3.getInt(1);
						 String name=rset3.getString(2)+" "+rset3.getString(3);
						 viewers.put(uid,name);
					 }
						//populate data object		
						for(int p:urls.keySet())
						{
							ViewedPhotos vp = new ViewedPhotos();
							vp.setPhotoid(p);
							vp.setUrl(urls.get(p));
							ArrayList<Integer> viewids = new ArrayList<Integer>();
							ArrayList<String> pviewers = new ArrayList<String>();
							ArrayList<String> comments = new ArrayList<String>();
							
							for(ViewedType v:views)
							{
								if(v.getPhotoId()==p)
								{
									if(viewers.containsKey(v.getViewerId()))
									{
										viewids.add(v.getViewerId());
										pviewers.add(viewers.get(v.getViewerId()));
										comments.add(v.getComment());
										vp.setDesc(desc.get(v.getPhotoId()));
										
									}
									
								}
							}
							vp.setViewerids(viewids);
							vp.setViewers(pviewers);
							vp.setComments(comments);
							vieweds.add(vp);
							
						}
						
						 rset.close();
						 
						 rset3.close();
							pstmt.close();
							
							pstmt3.close();
							//conn.close();
				}
				//for photos with no viewers:
				
				
				 for(int p:urls.keySet())
					{
						ViewedPhotos vp = new ViewedPhotos();
						vp.setPhotoid(p);
						vp.setUrl(urls.get(p));					
						vp.setDesc(desc.get(p));						
						vieweds.add(vp);
					}
				
			}
			return vieweds;
		}
		
	public ArrayList<String> getCircleNames(int userId) throws Exception
	{
		ArrayList<String> cnames = new ArrayList<String>();
		PreparedStatement pstmt = conn.prepareStatement("SELECT distinct circlename FROM "+PennPhotoConstants.schemaName+"Circle WHERE ownerid = ?");
		pstmt.setInt(1,userId);
		ResultSet rset = pstmt.executeQuery();
		while(rset.next())
		{
			cnames.add(rset.getString(1));
		}
		return cnames;
	}
		
	public ArrayList<String> getFriendNames(int userId) throws Exception
	{
		ArrayList<String> fnames = new ArrayList<String>();
		PreparedStatement pstmt = conn.prepareStatement("SELECT firstname,lastname FROM "+PennPhotoConstants.schemaName+"users WHERE userid  IN (SELECT friendid FROM "+PennPhotoConstants.schemaName+"friends WHERE userid=?)");
		pstmt.setInt(1,userId);
		ResultSet rset = pstmt.executeQuery();
		while(rset.next())
		{
			fnames.add(rset.getString(1)+" "+rset.getString(2));
		}
		return fnames;
	}	

}
